﻿/***********************************COMB类型GUID支持函数***********************************/
IF exists(select name from sysobjects where name='GetCombId2' and type='fn')
  drop FUNCTION GetCombId2
go
CREATE FUNCTION GetCombId2(@Guid UNIQUEIDENTIFIER, @Time datetime)
	RETURNS UNIQUEIDENTIFIER
AS
BEGIN
	DECLARE @CombId UNIQUEIDENTIFIER, @TimePart BINARY(6)
	set @TimePart=CAST(@Time AS BINARY(6))
	SET @CombId = CAST(CAST(@Guid AS BINARY(10)) + @TimePart  AS UNIQUEIDENTIFIER)
	RETURN @CombId
END
GO


if exists(select name from sysobjects where name='GetCombId' and type='fn')
  drop FUNCTION GetCombId
go
CREATE FUNCTION GetCombId(@Guid UNIQUEIDENTIFIER)
	RETURNS UNIQUEIDENTIFIER
AS
BEGIN
	return dbo.GetCombId2(@Guid, getdate())
END
GO


/***********************************手工实现自增长主键***********************************/
/*此方案废止
-- 自增长主键储存表，记录当前已分配的最大主键值
if exists(select name from sysobjects where name='sysIntKey' and type='u')
   drop table sysIntKey
go
CREATE TABLE sysIntKey
(
  Type	VARCHAR(100) NOT NULL,
  Value	INT NOT NULL,
constraint pk_IntKey primary key(Type)
)
GO

-- 用存储过程来生成新的自增长键值
if exists(select name from sysobjects where name='GetKey' and type='p')
  drop procedure GetKey
go
CREATE PROCEDURE GetKey(@Type VARCHAR(100), @Value INT OUTPUT)
AS
  UPDATE sysIntKey SET @Value = Value = Value+1 WHERE Type = @Type
  IF @@ROWCOUNT=0
  BEGIN
    SET @Value=1
    INSERT INTO sysIntKey(Type, Value) VALUES(@Type, @Value)
  END
GO
-- EXEC GetKey 'TEST', @key output
*/

/***************************************流程模板***************************************/
if exists(select name from sysobjects where name='xfProcessTemplate' and type='u')
   drop table xfProcessTemplate
GO
-- 流程定义表
CREATE TABLE xfProcessTemplate
(
  Id				UNIQUEIDENTIFIER NOT NULL,	-- 主建
  Name				NVARCHAR(100) NOT NULL,		-- 名称
  IsSubProcess		BIT NOT NULL DEFAULT 0,		-- 是否子流程
  Invalid			BIT NOT NULL DEFAULT 0,		-- 作废标志
  RowVersion		INT NOT NULL,
  constraint pk_ProcessTemplate primary key(Id)
)
go

/***************************************流程实例***************************************/
if exists(select name from sysobjects where name='xfProcessInstance' and type='u')
   drop table xfProcessInstance
GO
-- 流程实例表
CREATE TABLE xfProcessInstance
(
  Id				UNIQUEIDENTIFIER NOT NULL,	-- 主建
  TemplateId		UNIQUEIDENTIFIER NOT NULL,	-- 流程定义(模板)主建
  OwnerProcessId	UNIQUEIDENTIFIER,			-- 归属流程ID(为NULL则是主流程，否则为子流程)
  Name				NVARCHAR(100) NOT NULL,		-- 名称
  [Status]			INT NOT NULL DEFAULT 0,
  StartTime			datetime,
  EndTime			datetime,
  Invalid			BIT NOT NULL DEFAULT 0,		-- 作废标志
  RowVersion		INT NOT NULL,
  constraint pk_ProcessInstance primary key(Id)
)
GO


/***************************************流程活动***************************************/
if exists(select name from sysobjects where name='xfActivity' and type='u')
   drop table xfActivity
GO
-- 流程活动(节点)表
CREATE TABLE xfActivity
(
  Id			UNIQUEIDENTIFIER NOT NULL,
  [Type]		VARCHAR(50) NOT NULL,	-- 活动类型(程序中声明的类名，可省略Activity后缀)，如：StartActivity可简记为Start
  ProcessId		UNIQUEIDENTIFIER NOT NULL,
  Code			NVARCHAR(20) NOT NULL,
  Name			NVARCHAR(50) NOT NULL,
  [Status]		INT NOT NULL DEFAULT 0,
  EntryTime		datetime,
  LeaveTime		datetime,
  Invalid		BIT NOT NULL DEFAULT 0,		-- 作废标志
  RowVersion	INT NOT NULL,
  CONSTRAINT pk_Activity PRIMARY KEY(Id)
)
GO
create index idx_Activity_Type on xfActivity([Type])
GO


/***************************************邮件活动***************************************/
if exists(select name from sysobjects where name='xfEmailActivity' and type='u')
   drop table xfEmailActivity
GO
-- 邮件发送活动(节点)表
CREATE TABLE xfEmailActivity
(
  Id			UNIQUEIDENTIFIER NOT NULL,
  Recipients	NVARCHAR(1024),	-- 收件人
  [Subject]		NVARCHAR(100),	-- 收件人
  Content		NVARCHAR(2000),	-- 邮件内容
  CONSTRAINT pk_EmailActivity PRIMARY KEY(Id)
)
GO

if exists(select name from sysobjects where name='xfvEmailActivity' and type='v')
   drop view xfvEmailActivity
GO
-- 邮件活动视图
CREATE VIEW xfvEmailActivity
AS
  SELECT A.*, E.Recipients, E.[Subject], E.Content
    FROM xfActivity A
    JOIN xfEmailActivity E ON A.[Type]='Email' AND E.id=A.id
GO


/***************************************工作活动***************************************/
if exists(select name from sysobjects where name='xfWorkActivity' and type='u')
   drop table xfWorkActivity
GO
-- 工作活动(节点)表
CREATE TABLE xfWorkActivity
(
  Id			UNIQUEIDENTIFIER NOT NULL,
  Performers	NVARCHAR(1024),	-- 执行者
  Notifiers		NVARCHAR(1024),	-- 知会者
  CONSTRAINT pk_WorkActivity PRIMARY KEY(Id)
)
GO

if exists(select name from sysobjects where name='xfvWorkActivity' and type='v')
   drop view xfvWorkActivity
GO
-- 工作活动视图
CREATE VIEW xfvWorkActivity
AS
  SELECT A.*, B.Performers, B.Notifiers
    FROM xfActivity A
    JOIN xfWorkActivity B ON A.Type='Work' AND B.id=A.id
GO
  
/***************************************工作项***************************************/
if exists(select name from sysobjects where name='xfWorkitem' and type='u')
   drop table xfWorkitem
GO
-- 工作项表
CREATE TABLE xfWorkitem
(
  Id			UNIQUEIDENTIFIER NOT NULL,
  ActivityId	UNIQUEIDENTIFIER NOT NULL,
  [Readonly]	BIT NOT NULL DEFAULT 0,
  [Action]		VARCHAR(20),
  EntryTime		DATETIME,
  LeaveTime		DATETIME,
  Invalid		BIT NOT NULL DEFAULT 0,		-- 作废标志
  RowVersion	INT NOT NULL,
  CONSTRAINT pk_Workitem PRIMARY KEY(Id)
)
GO

if exists(select name from sysobjects where name='xfvWorkitem' and type='v')
   drop view xfvWorkitem
GO
-- 工作项视图
CREATE VIEW xfvWorkitem
AS
  SELECT I.Id, P.Id ProcessId, P.Name ProcessName, I.ActivityId, A.Code ActivityCode, A.Name ActivityName, A.Status ActivityStatus,
         I.Readonly, I.Action, I.EntryTime, I.LeaveTime, I.RowVersion
    FROM xfWorkitem I
    JOIN xfWorkActivity W ON W.id=I.ActivityId
    JOIN xfActivity A ON A.Type='Work' AND A.id=W.id
    JOIN xfProcessInstance P ON P.Id=A.ProcessId
GO



/***************************************流程迁转***************************************/
if exists(select name from sysobjects where name='xfTransition' and type='u')
   drop table xfTransition
GO
-- 工作项表
CREATE TABLE xfTransition
(
  Id			UNIQUEIDENTIFIER NOT NULL,
  Type			INT NOT NULL,
  Label			NVARCHAR(50),
  BizRule		NVARCHAR(1024),
  FromActivityId UNIQUEIDENTIFIER NOT NULL,
  ToActivityId	UNIQUEIDENTIFIER NOT NULL,
  Invalid		BIT NOT NULL DEFAULT 0,		-- 作废标志
  RowVersion	INT NOT NULL,
  CONSTRAINT pk_Transition PRIMARY KEY(Id)
)
GO


/*清除数据
DELETE FROM xfWorkitem;
DELETE FROM xfEmailActivity;
DELETE FROM xfActivity;
DELETE FROM xfProcessInstance;

*/
